﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DuLieu;
using System.Data.OleDb;

namespace DAO
{
    public static class DuLieuTextDAO
    {
        public static List<DuLieuTextDTO> TimDuLieuTongHop(String tenDV, int maLoaiDV, int maDuong, int maPhuong, int maQuan)
        {
            List<DuLieuTextDTO> resultList = null;

            string sqlSelect = "SELECT tdd.TenDiaDiem, dv.TenDichVu, dl.SoNha, d.TenDuong, p.TenPhuong, q.TenQuanHuyen, dl.ChuThich ";
            string sqlFrom   = "FROM DuLieu dl,TenDiaDiem tdd, DichVu dv, Duong d, Phuong p, QuanHuyen q ";
            string sqlWhere  = "WHERE tdd.MaTenDiaDiem = dl.MaTenDiaDiem and (tdd.TenDiaDiem like '%" + tenDV + "%' ";

            //Lấy chuỗi từ khóa
            List<String> keyWords = Util.GetKeyWords(tenDV);
            if (keyWords != null && keyWords.Count > 0)
            {
                for (int i = 0; i < keyWords.Count; i++)
                    sqlWhere += " or tdd.TenDiaDiem like '%" + keyWords[i] + "%' ";
            }
            sqlWhere += ") ";

            string dichvu = "";
            string duong = "";
            string phuong = "";
            string quan = "";

            //Câu SQL cho Loại Dịch Vụ
            if (maLoaiDV != -1)
            {
                dichvu = "and dv.MaDichVu = " + maLoaiDV;
                sqlWhere += dichvu;
            }
            sqlWhere += " and dv.MaDichVu = dl.MaDichVu ";

            //Câu SQL cho Đường
            if (maDuong != -1)
            {
                duong = "and d.MaDuong = " + maDuong;
                sqlWhere += duong;
            }
            sqlWhere += " and d.MaDuong = dl.MaDuong ";

            //Câu SQL cho Phường
            if (maPhuong != -1)
            {
                phuong = "and p.MaPhuong = " + maPhuong;
                sqlWhere += phuong;
            }
            sqlWhere += " and p.MaPhuong = dl.MaPhuong ";

            //Câu SQL cho Quận
            if (maQuan != -1)
            {
                quan = "and q.MaQuanHuyen = " + maQuan;
                sqlWhere += quan;
            }
            sqlWhere += " and q.MaQuanHuyen = dl.MaQuanHuyen ";

            String sql = sqlSelect + sqlFrom + sqlWhere;

            try
            {
                DataProvider.OpenConnection();

                resultList = new List<DuLieuTextDTO>();

                OleDbDataReader reader = DataProvider.ExecuteReader(sql);

                while (reader.Read())
                {
                    DuLieuTextDTO dl = new DuLieuTextDTO();
                    string soNha = "";
                    string tenDuong = "";
                    string tenPhuong = "";
                    string tenQuanHuyen = "";
                    
                    if (!reader.IsDBNull(0)) dl.TenDiaDiem = reader.GetString(0);
                    if (!reader.IsDBNull(1)) dl.TenDichVu = reader.GetString(1);

                    if (!reader.IsDBNull(2))
                    {
                        soNha = reader.GetString(2);
                        dl.DiaChi += soNha += " - ";                        
                    }
                    if (!reader.IsDBNull(3))
                    {
                        tenDuong = reader.GetString(3);
                        dl.DiaChi += "đ.";
                        dl.DiaChi += tenDuong += " ";
                    }
                    if (!reader.IsDBNull(4))
                    {
                        tenPhuong = reader.GetString(4);
                        dl.DiaChi += ", P. ";
                        dl.DiaChi += tenPhuong += " ";
                    }
                    if (!reader.IsDBNull(5))
                    {
                        tenQuanHuyen = reader.GetString(5);
                        dl.DiaChi += ", Q. ";
                        dl.DiaChi += tenQuanHuyen;
                    }
                    
                    if (!reader.IsDBNull(6)) dl.ChuThich = reader.GetString(6);

                    resultList.Add(dl);

                    //if(resultList.Count > 10)
                    //    {
                    //    int tem = 0;
                    //    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return resultList;
        }
    }
}
